/**
 * JDBC Data Access Object Class
 * 
 * */
package com.allergan.dao;

import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

import javax.activation.DataHandler;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.allergan.model.AGNCaseDetail;

public class AGNDao {
	private static final Logger logger = LoggerFactory.getLogger(AGNDao.class);

	private Connection con;
	private Statement stmt;
	private ResultSet rs;
	
	/**
	 * Method to read a case detail row
	 * @param caseNum
	 * @param lotNum
	 * @param sampleInd
	 * @param status
	 * @return AGNCaseDetail object
	 */
	public AGNCaseDetail getAGNCaseDetail(String caseNum, String lotNum, 
			String sampleInd, String status)  {
		AGNCaseDetail agnCaseDetailQCMS = new AGNCaseDetail();
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			// Creating our connection
			con = DriverManager.getConnection("jdbc:oracle:thin:@hpusir61:1521:TARGUS", "ARGUS_AGN", "ARGUS_AGN");
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM V$AGN_CASE_DETAILS_QCMS" +
					" WHERE CASE_NUM = '" + caseNum + "'" +
					" AND LOT_NUM = '" + lotNum  + "'" +
					" AND SAMPLE_IND = '" + sampleInd  + "'" + 
					" AND STATUS = '" + status  + "'");
			Date sysdate = new java.sql.Date((new java.util.Date()).getTime());
			while (rs.next()) {
				if(status == "Pending") {
					status = "In Progress";
				}
				updateTestStatus(rs.getInt("REQUEST_ID"), 
							status, rs.getString("INITIATOR_REASON"), "", sysdate);
				
				agnCaseDetailQCMS.setStatus(status);
				agnCaseDetailQCMS.setStatusDate(sysdate);
				
				agnCaseDetailQCMS.setCaseNum(rs.getString("CASE_NUM"));
				agnCaseDetailQCMS.setLotNum(rs.getString("LOT_NUM"));
				agnCaseDetailQCMS.setSampleInd(rs.getString("SAMPLE_IND"));
				agnCaseDetailQCMS.setRequestId(rs.getInt("REQUEST_ID"));
				agnCaseDetailQCMS.setCaseId(rs.getInt("CASE_ID"));
				agnCaseDetailQCMS.setProductName(rs.getString("PRODUCT_NAME"));
				agnCaseDetailQCMS.setLotExpDate(rs.getDate("LOT_EXP_DATE"));
				agnCaseDetailQCMS.setListedness(rs.getInt("LISTEDNESS"));
				agnCaseDetailQCMS.setSeriousness(rs.getInt("SERIOUSNESS"));
				agnCaseDetailQCMS.setLabTestSite(rs.getString("LAB_TEST_SITE"));
				agnCaseDetailQCMS.setLabTestSiteNum(rs.getString("LAB_TEST_SITE_NUM"));
				agnCaseDetailQCMS.setInitiatorName(rs.getString("INITIATOR_NAME"));
				agnCaseDetailQCMS.setInitiatorEmail(rs.getString("INITIATOR_EMAIL"));
				agnCaseDetailQCMS.setInitiatorReason(rs.getString("INITIATOR_REASON"));
				agnCaseDetailQCMS.setInitiatorDate(rs.getDate("INITIATOR_DATE"));
				agnCaseDetailQCMS.setInitiatorNote(rs.getString("INITIATOR_NOTE"));
				agnCaseDetailQCMS.setComplaintDescr(rs.getString("COMPLAINT_DESCR"));
				agnCaseDetailQCMS.setStatusReason(rs.getString("STATUS_REASON"));
				agnCaseDetailQCMS.setCumSpecResult(rs.getString("CUM_SPEC_RESULT"));
				agnCaseDetailQCMS.setEvents(rs.getString("EVENTS"));
				agnCaseDetailQCMS.setCountry(rs.getString("COUNTRY"));
				agnCaseDetailQCMS.setInitReptDate(rs.getDate("INIT_REPT_DATE"));
				agnCaseDetailQCMS.setClassification(rs.getString("CLASSIFICATION"));
				agnCaseDetailQCMS.setProductId(rs.getInt("PRODUCT_ID"));
				agnCaseDetailQCMS.setXNumber(rs.getString("XNUMBER"));
				
				
			}
			close();
			return agnCaseDetailQCMS;
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Method to update the test status
	 * @param requestId
	 * @param status
	 * @param initiatorReason
	 * @param results
	 * @param statusDate
	 * @return status
	 */
	public String updateTestStatus(int requestId, String status, String initiatorReason, String results, Date statusDate) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			// Creating our connection
			con = DriverManager.getConnection("jdbc:oracle:thin:@hpusir61:1521:TARGUS", "ARGUS_AGN", "ARGUS_AGN");
			
			String jobSQL = "BEGIN PKG_AGN_LAB_TEST.P_UPDATE_TEST_STATUS(?,?,?,?,?); END;";
	        CallableStatement callStmt = con.prepareCall(jobSQL);
	        
	        callStmt.setInt(1, requestId);
	        callStmt.setString(2, status);
	        callStmt.setString(3, initiatorReason);
	        callStmt.setString(4, results);
	        callStmt.setDate(5, statusDate);
	        
	        callStmt.execute();
	        
	        return "Update Success!";
		} catch (Exception e) {
			logger.error(e.getMessage());
			// e.printStackTrace();
			return "Error: " + e.getMessage();
		}
	}
	
	/**
	 * Method to mark the request as completed,
	 * calls P_LABTEST_COMPLT_IN_SPEC
	 * @param requestId
	 * @return status
	 */
	public String markCompletedInSpec(int requestId, String status, String statusReason, String cumSpecResult, Date statusDate) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			// Creating our connection
			con = DriverManager.getConnection("jdbc:oracle:thin:@hpusir61:1521:TARGUS", "ARGUS_AGN", "ARGUS_AGN");
			
			String jobSQL = "BEGIN PKG_AGN_LAB_TEST_INT.P_LABTEST_COMPLT_IN_SPEC(?); END;";
	        CallableStatement callStmt = con.prepareCall(jobSQL);
	        callStmt.setInt(1, requestId);
	        callStmt.execute();
	        
	        return "Success!";
		} catch (Exception e) {
			logger.error(e.getMessage());
			// e.printStackTrace();
			return "Error: " + e.getMessage();
		}
	}
	
	/**
	 * Method to mark the request as completed,
	 * calls P_LABTEST_COMPLT_OUT_OF_SPEC
	 * @param requestId
	 * @return status
	 */
	public String markCompletedOutOfSpec(int requestId, String status, String statusReason, String cumSpecResult, Date statusDate) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			// Creating our connection
			con = DriverManager.getConnection("jdbc:oracle:thin:@hpusir61:1521:TARGUS", "ARGUS_AGN", "ARGUS_AGN");
			
			String jobSQL = "BEGIN PKG_AGN_LAB_TEST_INT.P_LABTEST_COMPLT_OUT_OF_SPEC(?); END;";
	        CallableStatement callStmt = con.prepareCall(jobSQL);
	        callStmt.setInt(1, requestId);
	        callStmt.execute();
	        
	        return "Success!";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage());
			e.printStackTrace();
			return "Error: " + e.getMessage();
		}
	}
	
	/**
	 * Method to attach a report to the case,
	 * takes a java Blob as input
	 * @param caseNum
	 * @param classification
	 * @param fileName
	 * @param fileBlob
	 * @param notes
	 * @param keywords
	 * @param commitInd
	 * @return status
	 */
	public String attachReport(String caseNum, String classification, String fileName, DataHandler data, 
			String notes, String keywords, int commitInd) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			// Creating our connection
			con = DriverManager.getConnection("jdbc:oracle:thin:@hpusir61:1521:TARGUS", "ARGUS_AGN", "ARGUS_AGN");
			
			String jobSQL = "CALL PKG_AGN_INTERFACE.P_ATTACH_FILE(?,?,?,?,?,?,?); END;";
	        CallableStatement callStmt = con.prepareCall(jobSQL);
	        callStmt.setString(1, caseNum);
			callStmt.setString(2, classification);
			callStmt.setString(3, fileName);
			callStmt.setBlob(4, (Blob) data);
			callStmt.setString(5, notes);
			callStmt.setString(6, keywords);
			callStmt.setInt(7, commitInd);
			
			callStmt.execute();
	        
	        return "Success!";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage());
			e.printStackTrace();
			return "Error: " + e.getMessage();
		}
	}
	
	/* Closing the DB Connection */
	private void close() {
		try {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null) {
				stmt.close();
			}

			if (con != null) {
				con.close();
			}
		} catch (Exception e) {

		}
	}

}
